package cs304.p3.library.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Map;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;

import cs304.p3.library.domain.Borrower;
import cs304.p3.library.exceptions.AddBorrowerException;
import cs304.p3.library.util.SqlUtils;

public class BorrowerDao extends Dao {
	public void insertBorrower(String bid, String password, String name,
			String address, String phone, String emailAddress,
			String sinOrStNo, Date expiryDate, String type) throws AddBorrowerException, SQLException {

		StringBuilder sql = new StringBuilder("INSERT INTO Borrower VALUES");

		List<String> values = Lists.newArrayList();
		values.add(bid);
		values.add(password);
		values.add(name);
		values.add(address);
		values.add(phone);
		values.add(emailAddress);
		values.add(sinOrStNo);
		values.add(SqlUtils.getDateString(expiryDate));
		values.add(type);

		sql.append(SqlUtils.commaQuoteStrVal(values));

		System.out.println(sql.toString());
		PreparedStatement insert = connection.prepareStatement(sql.toString());
		insert.executeUpdate();
	}
	
	/**
	 * Gets a single borrower from the Borrower table.<br>
	 * This only works because we're selecting using its primary key.
	 */
	public Borrower selectBorrower(String bid, String password) throws SQLException {
		Map<String, String> values = Maps.newHashMap();
		values.put("bid", bid);
		values.put("password", password);
		String sql = SqlUtils.selectSql("Borrower", values);
		PreparedStatement select = connection.prepareStatement(sql);
		ResultSet rs = select.executeQuery();
		
		if(rs.next()) {
			Borrower borrower = new Borrower();
			borrower.setBid(rs.getString("bid"));
			borrower.setPassword(rs.getString("password"));
			borrower.setName(rs.getString("name"));
			borrower.setAddress(rs.getString("address"));
			borrower.setPhone(rs.getString("phone"));
			borrower.setEmailAddress(rs.getString("emailAddress"));
			borrower.setSinOrStNo(rs.getString("sinOrStNo"));
			borrower.setExpiryDate(rs.getDate("expiryDate"));
			borrower.setType(rs.getString("type"));
			
			return borrower;
		} else {
			// no such Borrower
			return null;
		}
	}

	public List<Borrower> selectBorrower(Map<String,String> keyVals) throws SQLException {
		String sql = SqlUtils.selectSql("Borrower", keyVals);
		System.out.println(sql);

		PreparedStatement select = connection.prepareStatement(sql);
		ResultSet rs = select.executeQuery();
		List<Borrower> borrowers = Lists.newArrayList();
		while(rs.next()) {
			Borrower borrower = new Borrower();
			borrower.setBid(rs.getString("bid"));
			borrower.setPassword(rs.getString("password"));
			borrower.setName(rs.getString("name"));
			borrower.setAddress(rs.getString("address"));
			borrower.setPhone(rs.getString("phone"));
			borrower.setEmailAddress(rs.getString("emailAddress"));
			borrower.setSinOrStNo(rs.getString("sinOrStNo"));
			borrower.setExpiryDate(rs.getDate("expiryDate"));
			borrower.setType(rs.getString("type"));

			borrowers.add(borrower);
		}
		return borrowers;
	}

	public Map<String,Integer> selectBorrowerTypes() throws SQLException {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM BorrowerType");
		PreparedStatement select = connection.prepareStatement(sql.toString());
		ResultSet rs = select.executeQuery();
		Map<String,Integer> typeLimit = Maps.newHashMap();
		while(rs.next()) {
			typeLimit.put(rs.getString("type"), rs.getInt("bookTimeLimit"));
		}
		return typeLimit;
	}

	// generateBid generates an 8 digit unique bid of the form : xxxxyyyy
	// xxxx = random 4 digits
	// yyyy = padded unique sequence
	public String generateBid() throws SQLException {
		String generateBid = "SELECT LPAD(LPAD(TO_CHAR(bid_counter.NEXTVAL),4,'0'),8,ROUND(DBMS_RANDOM.VALUE(1000,9999))) FROM DUAL";
		PreparedStatement ps;
		String bid = null;
		ps = connection.prepareStatement(generateBid);
		ResultSet rs = ps.executeQuery();
		rs.next();
		bid = rs.getString(1);

		return bid;
	}

	public String generatePassword() throws SQLException {
		String generatePassword = "SELECT ROUND(DBMS_RANDOM.VALUE(1000,9999)) FROM DUAL";
		PreparedStatement ps;
		String password = null;

		ps = connection.prepareStatement(generatePassword);
		ResultSet rs = ps.executeQuery();
		rs.next();
		password = rs.getString(1);

		return password;
	}
}
